package db;

import houseHold.House;
import houseHold.HouseHoldObject;
import houseHold.Room;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.util.Vector;

import person.Person;
import person.PersonAchive;
import sensor.Sensor;
import constants.Constants;

public class DBConnection {
	public static Connection conn = null;

	public static void initConnection() {
		conn = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "test";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "";
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void closeConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void removeSimData(String data) {
		readyInserts();
		String insert = " DELETE FROM " + simData
				+ " where tableName='SimulationData_" + data + "'";
		try {
			st.executeUpdate(insert);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Vector<String> getSimData(String id) {
		Vector<String> res = new Vector<String>();
		readyInserts();
		String sid = "SimulationData_" + id;
		String selectString = "SELECT tableName  FROM `" + simData
				+ "` WHERE tableName REGEXP '^" + sid + "'";
		System.out.println(selectString);
		try {
			st2 = conn.createStatement();
			st2.executeQuery(selectString);
			ResultSet rs = st2.getResultSet();
			if (rs != null) {
				while (rs.next()) {
					String r = rs.getString(1);
					res.add(r);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return res;
	}

	public static void addSimData(String data) {
		readyInserts();
		String insert = " INSERT INTO " + simData
				+ " (tableName)	VALUES ('SimulationData_" + data + "')";
		try {
			st.executeUpdate(insert);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static String data = "SimulationData_", sensor = "SensorData_",
			roomBindings = "RoomBindings_", SensorBindings = "SensorBindings_",
			object = "ObjectData_", people = "PeopleData_",
			roomData = "RoomData_";
	private static String simData = "SimulationList";

	public static void CreateDataTable(String date) {
		data = "SimulationData_";
		data += Constants.id + "_" + date.replace("-", "_");
		String drop = "DROP  TABLE IF EXISTS " + data;
		String dataTable = "CREATE TABLE " + data
				+ " (regDate DateTime,sensor_ID VARCHAR(100),value FLOAT)";
		try {
			st = conn.createStatement();
			st.execute(drop);
			st.executeUpdate(dataTable);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void CreateTables() {
		data = "SimulationData_";
		sensor = "SensorData_";
		roomBindings = "RoomBindings_";
		SensorBindings = "SensorBindings_";
		object = "ObjectData_";
		people = "PeopleData_";
		roomData = "RoomData_";
		String tid = Constants.id;
		data += tid;
		sensor += tid;
		roomBindings += tid;
		SensorBindings += tid;
		object += tid;
		people += tid;
		roomData += tid;
		String drop = "DROP  TABLE IF EXISTS " + sensor + " , " + roomBindings
				+ " , " + SensorBindings + " , " + object + " , " + people
				+ " , " + roomData;

		String sensorTable = "CREATE TABLE " + sensor
				+ " (sensor_ID VARCHAR(100),sensor_Type VARCHAR(100))"; // done
		String roomBindingTable = "CREATE TABLE " + roomBindings
				+ " (room_ID INT,sensor_ID VARCHAR(100))"; // done
		String roomDataTable = "CREATE TABLE "
				+ roomData
				+ " (room_ID INT,room_name VARCHAR(100),height INT,length INT,width INT,room_type VARCHAR(100))";// done
		String SensorBindingTabler = "CREATE TABLE " + SensorBindings
				+ " (sensor_ID VARCHAR(100),object_ID INT)"; // done
		String roomObjectTable = "CREATE TABLE "
				+ object
				+ " (room_ID INT,object_ID INT,object_type VARCHAR(100),consumption_type VARCHAR(100),average_consumption INT,object_name VARCHAR(100))";// done
		String peopleTable = "CREATE TABLE "
				+ people
				+ " (first_name VARCHAR(100),last_name  VARCHAR(100),age INT,RFID_ID varchar(100))";// done
		String simDataCreate = "CREATE TABLE IF NOT EXISTS " + simData
				+ " (tableName VARCHAR(255))"; // done
		Statement st;
		try {
			st = conn.createStatement();
			st.execute(drop);
			st.executeUpdate(roomBindingTable);
			st.executeUpdate(sensorTable);
			st.executeUpdate(roomDataTable);
			st.executeUpdate(SensorBindingTabler);
			st.executeUpdate(roomObjectTable);
			st.executeUpdate(peopleTable);
			st.executeUpdate(simDataCreate);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void insertMainData() {
		Statement st = null;
		try {
			st = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		/**
		 * Insert person information into table <people> & rfid sensors into
		 * table <sensor>
		 */
		for (int i = 0; i < PersonAchive.getCount(); i++) {
			Person p = PersonAchive.getPerson(i);
			String insertPeople = "INSERT INTO " + people
					+ " (first_name ,last_name ,age ,RFID_ID)	VALUES ('"
					+ p.getName() + "','" + p.getLastName() + "'," + p.getAge()
					+ ",'" + p.getrFid().getSensorName() + "')";
			String insertRfSensor = "INSERT INTO " + sensor
					+ " (sensor_ID,sensor_Type)" + "	VALUES ('"
					+ p.getrFid().getSensorName() + "','"
					+ p.getrFid().getType() + "')";
			try {
				st.execute(insertPeople);
				st.execute(insertRfSensor);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/**
		 * insert room information into table <roomData>
		 */
		for (int i = 0; i < House.getInstance().getRoomCount(); i++) {
			Room r = House.getInstance().getRoom(i);
			String insertRoom = "INSERT INTO " + roomData
					+ " (room_ID ,room_name ,height ,length ,width ,room_type)"
					+ "	VALUES (" + r.getID() + ",'" + r.getName() + "',"
					+ r.getHeight() + "," + r.getLength() + "," + r.getWidth()
					+ ",'" + r.getType() + "')";
			try {
				st.execute(insertRoom);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			/**
			 * Insert sensor information into table <sensorBindingTable> &
			 * <sensorTable>
			 */
			Set<String> ks = r.getSensors().keySet();
			Iterator<String> it = ks.iterator();
			while (it.hasNext()) {
				Sensor s = r.getSensors().get(it.next());
				String insertSensor = "INSERT INTO " + sensor
						+ " (sensor_ID,sensor_Type)" + "	VALUES ('"
						+ s.getSensorName() + "','" + s.getType() + "')";

				String insertIDs = "INSERT INTO " + roomBindings
						+ " (room_ID,sensor_ID)" + "	VALUES (" + r.getID()
						+ ",'" + s.getSensorName() + "')";
				try {
					st.execute(insertSensor);
					st.execute(insertIDs);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

			ks = r.getItemList().keySet();
			it = ks.iterator();
			/**
			 * Insert object information into table <SensorBindings> & <object>
			 */
			while (it.hasNext()) {
				HouseHoldObject o = r.getItem(it.next());
				String insertSensorBindings = "INSERT INTO " + SensorBindings
						+ " (sensor_ID,object_ID)" + "	VALUES ('"
						+ o.getSensor().getSensorName() + "'," + o.getID()
						+ ")";

				String insertObject = "INSERT INTO "
						+ object
						+ "(room_ID ,object_ID ,object_type ,consumption_type ,average_consumption ,object_name )	"
						+ "VALUES (" + r.getID() + "," + o.getID() + ",'"
						+ o.getType() + "','" + o.getConsumptionType() + "',"
						+ o.getConsumption() + ",'" + o.getName() + "')";
				try {
					st.execute(insertSensorBindings);
					st.execute(insertObject);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}

	private static Statement st = null;

	public static void readyInserts() {
		try {
			st = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}

	public static void closeInserts() {
		try {
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void insertData(String time, double value, String sensorID) {
		String insert = " INSERT INTO " + data + " (regDate,sensor_ID,value)"
				+ "	VALUES ('" + time + "','" + sensorID + "'," + value + ")";
		// System.out.println(insert);
		try {
			st.execute(insert);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static double fVal = 144.18784511;

	public static ResultSet select(String tableName, String type,
			boolean formula) {
		String selectString = new String();
		String value = "value";
		if (formula) {
			value = value + "*" + fVal;
		}
		selectString = "SELECT time(regDate)," + value + " FROM `" + tableName
				+ "` " + " WHERE sensor_ID REGEXP '^" + type + "'";
		ResultSet rs = null;
		try {
			st2 = conn.createStatement();
			rs = st2.executeQuery(selectString);

		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return rs;
	}

	public static void initMap() {
		dataMap = new HashMap<Integer, Double>();
	}

	public static HashMap<Integer, Double> getData() {
		return dataMap;
	}

	private static HashMap<Integer, Double> dataMap;

	public static HashMap<Integer, Double> build(ResultSet rs) {
		try {
			while (rs.next()) {
				Time t = rs.getTime(1);
				Calendar c = Calendar.getInstance();
				Double value = rs.getDouble(2);
				c.setTime(t);
				int h, m, s;
				h = c.get(Calendar.HOUR_OF_DAY);
				m = c.get(Calendar.MINUTE);
				s = c.get(Calendar.SECOND);
				int val = h * 3600 + m * 60 + s;
				if (dataMap.get(val) == null) {
					dataMap.put(val, value);
				} else {
					dataMap.put(val, dataMap.get(val) + value);
				}

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return dataMap;
	}

	private static Statement st2;

	public static void main(String args[]) {
		initConnection();
		String drop = "DROP  TABLE IF EXISTS " + simData;

		String simDataCreate = "CREATE TABLE " + simData
				+ " (tableName VARCHAR(255))"; // done
		Statement st;
		try {
			st = conn.createStatement();
			st.execute(drop);
			st.executeUpdate(simDataCreate);
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

}